set lines 180
set pages 10000
col command for a180

SELECT 'CREATE INDEX ' || cons.constraint_name || ' ON ' || cons.table_name || '(' || cons.columns || ') TABLESPACE &tablespace;' command
FROM (SELECT constraint_name, table_name,
           LTRIM(MAX(SYS_CONNECT_BY_PATH(column_name,',')) KEEP (DENSE_RANK LAST ORDER BY curr),',') AS columns
      FROM (SELECT dcc.constraint_name,
                   dcc.table_name,
                   dcc.column_name,
                   ROW_NUMBER() OVER (PARTITION BY dcc.constraint_name ORDER BY dcc.position) AS curr,
                   ROW_NUMBER() OVER (PARTITION BY dcc.constraint_name ORDER BY dcc.position) -1 AS prev
            FROM  user_cons_columns dcc
            INNER JOIN user_constraints dc
                    ON dc.owner = dcc.owner
                   AND dc.constraint_name = dcc.constraint_name
                   AND dcc.table_name = dc.table_name
            WHERE dc.constraint_type = 'R')
    GROUP BY constraint_name, table_name
    CONNECT BY prev = PRIOR curr AND constraint_name = PRIOR constraint_name
    START WITH curr = 1) cons
WHERE NOT EXISTS (SELECT 1 
        FROM 
          (SELECT index_name, table_name,
                  LTRIM(MAX(SYS_CONNECT_BY_PATH(column_name,',')) KEEP (DENSE_RANK LAST ORDER BY curr),',') AS columns
             FROM (SELECT icc.index_name,
                          icc.table_name,
                          icc.column_name,
                          ROW_NUMBER() OVER (PARTITION BY icc.index_name ORDER BY icc.column_position) AS curr,
                          ROW_NUMBER() OVER (PARTITION BY icc.index_name ORDER BY icc.column_position) -1 AS prev
                    FROM  user_ind_columns icc)
             GROUP BY index_name, table_name
             CONNECT BY prev = PRIOR curr AND index_name = PRIOR index_name
             START WITH curr = 1) indx
        WHERE cons.table_name = indx.table_name
          AND cons.columns = indx.columns)
ORDER BY cons.table_name;